﻿using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.IO;
using System.Linq;
namespace WPFExcelReport
{
    public class CaseInsensitiveComparer : IComparer<object>
    {
        public int Compare(object x, object y)
        {
            return string.Compare((string)x, (string)y, StringComparison.OrdinalIgnoreCase);
        }
    }
    class YCACOFSReport : BaseReport
    {
        #region ----------------PROPERTIES----------------------------------
        public OracleConnection OracleDB;
        public OracleCommand orlReadCommand = new OracleCommand();
        public IntexReptServBLClient proxy;
        private Dictionary<string, object> dicParamater;
        public DataSet dsResult;
        public DataSet setUI = new DataSet();
        string ABPAUFFKRS;
        string ABPAUFFLFD;
        string ABPAUFFPOS;
        string WBBTP;
        string DocType;
        string CellName = "";
        string CellValue = "";
        string strCompany;
        string strExcelFile;
        string Today
        {
            get
            {
                DateTime now = DateTime.Now;
                return now.Year.ToString() + Right("00" + now.Month.ToString(), 2) + Right("00" + now.Day.ToString(), 2);
            }
        }
        #endregion
        public YCACOFSReport()
            : base()
        {
        }
        public YCACOFSReport(Dictionary<string, object> dicParamater)
            : base(dicParamater)
        {
            DocType = dicParamater["DocType"].ToString().Trim();
            strCompany = dicParamater["Company"].ToString().Trim();
            strExcelFile = dicParamater["ExcelFile"].ToString().Trim();
            Root = "C:\\IntexReport";
        }
        public override void WriteExcelReport(string fileType)
        {
            bool IsFTP = false;
            try
            {
                strCompany = "YCA";
                strExcelFile = @"YCA\YCACOFS.xls";
                Standard = "COFS";
                TemplatePath = Path.Combine(PathHelper.ApplicationPath, @"Report\YCA\YCACOFS.xls");
                destfolder = SetFolder(Root, "成本");
                
                DataRow dwOrder = dsSource.Tables["ABKOPF"].Rows[0];
                string KRS = dwOrder["ABKAUFKRS"].ToString().Trim();
                string LFD = dwOrder["ABKAUFLFD"].ToString().Trim();                
            
                string newfilename = KRS.Trim() +"-"+Helpers.Helper.GetSubRollNum(LFD);
                exAgent.CheckWorkBook(newfilename);
                ServerShareFile(KRS + LFD, fileType);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInEXCEL()
        {
            try
            {
                string value = "";
                DataRow row = null;
                decimal colorqty = 0;
                decimal colormvalue = 0;
                decimal colorprod = 0;
                decimal totalqty = 0;
                decimal totalvalue = 0;
                int x = 1, y = 1;
                x = 5; y = 14;
                row = dsSource.Tables["ABKOPF"].Rows[0];
                value = row["ABKAUFKRS"].ToString() + "-" + row["ABKAUFLFD"].ToString();
                exAgent.SetCellValue(x, y, value);

                x = 3; y = 30;
                exAgent.SetCellValue(x, y, Today); 
                int rown = -1;
                string processed = "";
                int posx = 18;
                int rpl = 9;
                int count = dsSource.Tables["ICC_ABPOS_MCO"].Rows.Count;
                foreach (DataRow dwABCO in dsSource.Tables["ICC_ABPOS_MCO"].Rows)
                {
                    exAgent.SetCellValue(posx, 1, "颜色"); exAgent.SetCellValue(posx, 6, "加工"+Environment.NewLine+"费用");
                   exAgent.SetCellValue(posx, 11, "加工"+Environment.NewLine+"单价");
                   exAgent.SetCellValue(posx, 14, "原料" + Environment.NewLine + "费用");
                   exAgent.SetCellValue(posx, 21, "总件数");
                   exAgent.SetCellValue(posx, 24, "尺码数量");
                   exAgent.SetCellValue(posx + 1, 24, "S"); exAgent.SetCellValue(posx + 1, 27, "M"); exAgent.SetCellValue(posx + 1, 30, "L");
                   exAgent.SetCellValue(posx + 1, 33, "XL");
                   exAgent.SetCellValue(posx + 4, 1, "总费用(加工费+原料费):");
                   exAgent.SetCellValue(posx + 4, 24, "单价(元/件):");
                   exAgent.SetCellValue(posx + 5, 1, "物料清单");
                    exAgent.SetCellValue(posx + 6, 1, "类型");
                    exAgent.SetCellValue(posx + 6, 3, "物料");
                    exAgent.SetCellValue(posx + 6, 10, "颜色/尺码");
                    exAgent.SetCellValue(posx + 6, 15, "标准量");
                    exAgent.SetCellValue(posx + 6, 19, "计划量");
                    exAgent.SetCellValue(posx + 6, 23, "实际量");
                    exAgent.SetCellValue(posx + 6, 27, "采购单价");
                    exAgent.SetCellValue(posx + 6, 31, "采购单");
                    																																	

                    string art = dwABCO["ART"].ToString();
                    string var = dwABCO["VAR"].ToString();
                    var = Left(var, 2);
                    string color = dwABCO["FARBE"].ToString();
        
                    if (rown == -1)
                    {            
                        string enddate = dwABCO["ENDDATE"].ToString();
                        x = 4; y = 30;
                        exAgent.SetCellValue(x, y, enddate);//加工完成日期
                        string APPDATE = dwABCO["APPDATE"].ToString();
                        x = 5; y = 30;
                        exAgent.SetCellValue(x, y, APPDATE);//评估日期:          
                        x = 3; y = 4;
                        exAgent.SetCellValue(x, y, art);
                        x = 3; y = 9;
                        exAgent.SetCellValue(x, y, var);
                        value = "品名:" + GetGB2312Value(dwABCO["ARTDES"].ToString());
                        x = 3; y = 14;
                        exAgent.SetCellValue(x, y, value);
                    }
                 
                    #region====Color===================
                 
                    if (!processed.Contains(color + ";"))
                    {
                        processed += color + ";";
                        rown++;
                        x = posx + 2; y = 1;
                        exAgent.SetCellValue(x, y, color);
                        value = GetGB2312Value(dwABCO["CDES"].ToString());
                        x = posx + 2 + 1; y = 1;
                        exAgent.SetCellValue(x, y, value);
                    }
                    exAgent.SetCellValue(posx + 2, 24, dwABCO["S"].ToString());
                    exAgent.SetCellValue(posx + 2, 27, dwABCO["M"].ToString());
                    exAgent.SetCellValue(posx + 2, 30, dwABCO["L"].ToString());
                    exAgent.SetCellValue(posx + 2, 33, dwABCO["XL"].ToString());
                    exAgent.SetCellValue(posx + 2 + 1, 24, dwABCO["SP"].ToString());
                    exAgent.SetCellValue(posx + 2 + 1, 27, dwABCO["MP"].ToString());
                    exAgent.SetCellValue(posx + 2 + 1, 30, dwABCO["LP"].ToString());
                    exAgent.SetCellValue(posx + 2 + 1, 33, dwABCO["XLP"].ToString());
                    #endregion 
                    exAgent.SetCellValue(posx + 2, 6, dwABCO["PRODV"].ToString());
                    exAgent.SetCellValue(posx + 2 + 1, 11, dwABCO["MPRICE"].ToString());
                    colormvalue = System.Convert.ToDecimal(dwABCO["VMQACTPEK"].ToString())/100;
                    exAgent.SetCellValue(posx + 2, 14, colormvalue);
                    exAgent.SetCellValue(posx + 2 + 1, 14, System.Convert.ToDecimal(dwABCO["VMQSTDPEK"].ToString())/100);

                    exAgent.SetCellValue(posx + 2, 21, dwABCO["QTY"].ToString());
                    exAgent.SetCellValue(posx + 2 + 1, 21, dwABCO["QTYP"].ToString());
                    colorqty = System.Convert.ToDecimal(dwABCO["QTY"].ToString());
                    colorprod = System.Convert.ToDecimal(dwABCO["PRODV"].ToString());                    
                   
                    exAgent.SetCellValue(posx + 2 + 2, 18, System.Math.Round((colormvalue + colorprod), 0));
                    if (colorqty > 0)
                    {
                        exAgent.SetCellValue(posx + 2 + 2, 30, System.Math.Round((colormvalue + colorprod) / colorqty, 2));
                    }
                    totalqty += colorqty;
                    totalvalue += colormvalue + colorprod;
                    posx = FillInXDECK(posx + rpl-2, 1, dwABCO);
                    posx++;
                    if (rown < count - 1)
                    {
                        exAgent.RangeCopy(18, 18 + rpl - 1, posx, posx+ rpl-1);
                    }                    
                }
                if (totalqty > 0)
                {
                    exAgent.SetCellValue(5, 5, System.Math.Round(totalvalue / totalqty, 2));
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private int FillInXDECK(int posx, int starty, DataRow dwABCO)
        {
            int rown = -1;
            int x = posx;
            int y = starty;
            int rpl = 2;
            try
            {
                DataTable tb = dsSource.Tables["ICC_ABAUFM"];
                var query = (from rw in tb.AsEnumerable()
                             where rw["ABPART"].ToString()==dwABCO["ART"].ToString()
                             && rw["ABPVAR"].ToString().Trim() == dwABCO["VAR"].ToString().Trim()
                             && rw["ABPFARBE"].ToString() == dwABCO["FARBE"].ToString()
                             group rw by new
                             {
                                 
                                 MTYPE = rw["MTYPE"].ToString(),
                                 ART = rw["ABAART"].ToString(),
                                 VAR = rw["ABAVAR"].ToString(),
                                 COLOR = rw["ABAFARBE"].ToString(),
                                 ASIZE = rw["ABAGROE"].ToString(),
                             }
                                 into g
                                 select new
                                 {
                                     KEY = g.Key,
                                     ARTDES = g.Max(rw => rw.Field<string>("ARTDES")),
                                     CDES = g.Max(rw => rw.Field<string>("CDES")),
                                     EKPAUF = g.Max(rw => (rw.Field<string>("ABAAUFKRS") + rw.Field<string>("ABAAUFLFD") + rw.Field<string>("ABAAUFPOS"))),                                     
                                     QTYPLAN = g.Sum(rw => rw.Field<decimal>("QTYPLAN")),
                                     QTYACT = g.Sum(rw => rw.Field<decimal>("QTYACT")),
                                     QTYSTD = g.Sum(rw => rw.Field<decimal>("QTYSTD")),                                     
                                     PRSTD = g.Max(rw => rw.Field<decimal>("PRSTD")),
                                     PREK = g.Max(rw => rw.Field<decimal>("PREK")),
                                     PRPLAN = g.Max(rw => rw.Field<decimal>("PRPLAN")),                                   
                                     VQTYSTDPRSTD = g.Sum(rw => rw.Field<decimal>("VQTYSTDPRSTD")),
                                     VQTYSTDPREK = g.Sum(rw => rw.Field<decimal>("VQTYSTDPREK")),
                                     VQTYSTDPRPLAN = g.Sum(rw => rw.Field<decimal>("VQTYSTDPRPLAN")),
                                     VQTYPLANPRSTD = g.Sum(rw => rw.Field<decimal>("VQTYPLANPRSTD")),                                     
                                     VQTYPLANPREK = g.Sum(rw => rw.Field<decimal>("VQTYPLANPREK")),
                                     VQTYPLANPRPLAN = g.Sum(rw => rw.Field<decimal>("VQTYPLANPRPLAN")),
                                     VQTYACTPRSTD = g.Sum(rw => rw.Field<decimal>("VQTYACTPRSTD")),
                                     VQTYACTPREK = g.Sum(rw => rw.Field<decimal>("VQTYACTPREK")),
                                     VQTYACTPRPLAN = g.Sum(rw => rw.Field<decimal>("VQTYACTPRPLAN")),
                                 }).OrderBy(
                                 b => b.KEY.MTYPE, new CaseInsensitiveComparer())
                                 .ThenBy(b => b.KEY.ART, new CaseInsensitiveComparer())
                                .ThenBy(b => b.KEY.VAR, new CaseInsensitiveComparer())
                                .ThenBy(b => b.KEY.COLOR, new CaseInsensitiveComparer())
                                .ThenBy(b => b.KEY.ASIZE, new CaseInsensitiveComparer()); ;
               
                int count = query.Count();
                foreach (var rw in query)
                {
                    rown++;
                    string art = rw.KEY.ART.ToString();
                    string var = Left(rw.KEY.VAR.ToString() + "   ", 2);
                    string color = rw.KEY.COLOR.ToString();
                    exAgent.SetCellValue(x + rown * rpl, 1, rw.KEY.MTYPE);
                    exAgent.SetCellValue(x + rown * rpl, 3, rw.KEY.ART.ToString() + rw.KEY.VAR.ToString());
                    exAgent.SetCellValue(x + rown * rpl + 1, 3, GetGB2312Value(rw.ARTDES));
                    exAgent.SetCellValue(x + rown * rpl, 10, rw.KEY.COLOR.Trim() + "/" + rw.KEY.ASIZE);
                    exAgent.SetCellValue(x + rown * rpl + 1, 10, GetGB2312Value(rw.CDES));
                    exAgent.SetCellValue(x + rown * rpl, 31, rw.EKPAUF);
                      
                    exAgent.SetCellValue(x + rown * rpl, 15, System.Convert.ToDouble(rw.QTYSTD) * 0.01);
                    exAgent.SetCellValue(x + rown * rpl, 19, System.Convert.ToDouble(rw.QTYPLAN) * 0.01);
                    exAgent.SetCellValue(x + rown * rpl, 23, System.Convert.ToDouble(rw.QTYACT) * 0.01);
                    exAgent.SetCellValue(x + rown * rpl, 27, System.Convert.ToDouble(rw.PREK));
                    if (rown < count - 1)
                    {
                        exAgent.RangeCopy(posx, posx + rpl - 1, posx + (rown + 1) * rpl, posx + (rown + 1) * rpl + rpl);
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return posx + (rown + 1) * rpl;
        }   

        private string CreateFile(string orderno)
        {
            string filename = "";
            string sheetName = "";
            string Standard = "COFS";
            string filePath = Path.Combine(PathHelper.ApplicationPath, @"Report\YCA\YCACOFS.xls");
            try
            {
                string pwd = "";
                sheetName = orderno;
                exAgent.Open(filePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                exAgent.CreateNewSheet(orderno + ".xls", Standard, orderno.Replace(".xls", ""), pwd);
                filename = exAgent.FileName;
                FillInEXCEL();
                /*int startx = 53, starty = 1;
                //FillInWDECK(startx, starty);
                startx = 83;
                FillInMDECK(startx, starty);
                startx = 163;
                FillInFDECK(startx, starty);*/
                string localFile = exAgent.FileName;
                exAgent.Save();
                exAgent.Close();
                //System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return filename;
        }

        private void ServerShareFile(string ordernumer, string fileType, DataRow row = null)
        {
            string destfile, tempfile = "";
            DataRow dwABKOPF = dsSource.Tables["ABKOPF"].Rows[0];
            string fileabsname = "成本核算-" + ordernumer;
            filename = destfolder + "\\" + fileabsname + ".xls"; ;
            destfile = filename;         
            try
            {
                tempfile = CreateFile(ordernumer);
             
                if (File.Exists(destfile))
                {
                    File.Delete(destfile);
                }
                File.Move(tempfile, destfile);

                if (fileType == "OpenExcel")
                {
                    System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
    }
}